<?php
class Reports_model extends MY_Model
{
	public		
		$table = 'reports',
		$prKey = 'id'
	;
	
	function __construct()
	{
		parent::__construct();
		
		$this->fields = array(
			'param'			    => T_(''),
		);
		
	}
	
	
	function search($config = array())
	{ 
		$this->fields = array(
			'id' 				=> '',		
			'campaigns_count'	=> T_('К-во рекламных компаний (РК)'),
			'campaigns_activ'	=> T_('Активных РК'),
			'campaigns_moder' 	=> T_('Модерируемых РК'),
			'campaigns_stoped'	=> T_('Приостановленных РК'),
			'callflows_count'	=> T_('К-во голосовых потоков'),
			'adverts_count'	 	=> T_('К-во роликов (КР)'),			
			'adverts_active'	=> T_('В работе КР'),
			'adverts_moder'	 	=> T_('Модерируется КР'),
			'advertisers_count'	=> T_('К-во рекламодателей'),
			'users_count'	 	=> T_('Модерируется КР'),	
			'param'			    => T_('Модерируется КР'),
			
		);
		
		$this->rules = array(
			'campaigns_count'	=> "trim|strip_tags|required|max_length[10]",
			'campaigns_activ' 	=> "trim|strip_tags|required|max_length[255]",
			'campaigns_moder'	=> "trim|strip_tags|required|max_length[255]",
			'campaigns_stoped' 	=> "trim|strip_tags|required|max_length[255]",
			'callflows_count' 	=> "trim|strip_tags|required|integer|max_length[255]",
			'advertisers_count' 	=> "trim|strip_tags|max_length[100]",			
		);		
		
		$config = array_merge(array('rowsOnPage' => 5, 'order' => 'id'), $config);
		if ($this->input->post('per_page'))
		{
			$config['rowsOnPage'] = $this->input->post('per_page');
			$per_page = 0;
		}		
		else if ($this->input->get('per_page'))
		{
			$per_page = $this->input->get('per_page');
		}
		else
		{
			$per_page = 0;
		}

		$order 		= $this->input->get_post('order');
		$direction 	= $this->input->get_post('direction');
		$search 	= $this->input->get_post('search');
		$from_date 	= $this->input->get_post('from_date');
		$to_date 	= $this->input->get_post('to_date');

		// main sql		
		/*$sql = '';
		$sql .= ' FROM '.$this->table.' r, advertisers a';
		$sql .= ' WHERE r.id_advertisers=a.id ';  
			
		if ($search)
		{
			$sql .= ' AND r.name LIKE ' . $this->db->escape('%' . $search . '%'); 
		}
		
		switch ($order)
		{
			default:
				$sql .= ' ORDER BY r.id';
				$direction = 'DESC';
		}
		
		$sql .= ($direction == 'DESC') ? ' DESC' : ' ASC';*/

		// pagination config
		$config['base_url'] = Xurl_add(Xregenerate_url('per_page,search', true), 'search', $search);
		$config['per_page'] = $config['rowsOnPage'];
		//$config['total_rows'] = $this->getRowCount($sql);

		//$this->pagination->initialize($config); 
		
		// main sql
		$sql = 'select * from rpt_general();' ; 

		$this->data['list'] = $this->db->query($sql)->result();

		$this->data['per_page'] 	= $per_page;
		$this->data['search'] 		= $search;
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;
		
	}
	
	function search1($config = array())
	{ 
		$this->fields = array(
			'id' 		=> '',		
			'call_date'	=> T_('Дата звонка'),
			'calls_total'	=> T_('Контакты'),
			'withsms' 	=> T_('Отправленных SMS'),
			'withemail'	=> T_('Отправленных email'),
				
		);		
		
		
		$config = array_merge(array('rowsOnPage' => 5, 'order' => 'id'), $config);
		
		if ($this->input->post('per_page'))
		{
			$config['rowsOnPage'] = $this->input->post('per_page');
			$per_page = 0;
		}		
		else if ($this->input->get('per_page'))
		{
			$per_page = $this->input->get('per_page');
		}
		else
		{
			$per_page = 0;
		}

		$order 		= $this->input->get_post('order');
		$direction 	= $this->input->get_post('direction');
		$search 	= $this->input->get_post('search');
		$from_date 	= $this->input->get_post('from_date');
		$to_date 	= $this->input->get_post('to_date');
		

		if (empty($from_date)) {
			$from_date = date('Y-m-d');			
		}
		if (empty($to_date)) {
			$to_date = date('Y-m-d');			
		}		 
		
		
		
		// pagination config
		$config['base_url'] = Xurl_add(Xregenerate_url('per_page,search', true), 'search', $search);
		$config['per_page'] = $config['rowsOnPage'];	
		
		$this->pagination->initialize($config);
		
		// main sql
		$sql = " select * from rpt_quantitative_period('$from_date', '$to_date');"; 		

		$this->data['list'] = $this->db->query($sql)->result();
		$this->data['per_page'] 	= $per_page;
		$this->data['search'] 		= $search;
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;		
	}
	
	function stat($id = 0)
	{	
		$sql = "			
		select 
		sum(aa.num_calls) as num_adv_calls, 		 
		coalesce((select sum(amount)
		from stats.advertisers_payments 
		where id_advertisers = ".$id."
		and type = 0
		),0) as bonus_debit,
		sum(aa.num_sms) as num_sms, 		
		sum(aa.num_email) as num_email, 
		sum(aa.num_liked) as num_liked, 
		sum(aa.cost_total) as cost_total
		from advertisers a		
		left join campaigns c on c.id_advertisers = a.id
		left join stats.advertisers_all aa on aa.id_campaigns = c.id				
		where a.id = ".$id."
		limit 1"; 
		//".$id."
		$this->data['list'] = $this->db->query($sql)->row(); 
	}
	
	function search2($id = 0, $from_date = '', $to_date = '')
	{ 
		$this->fields = array(
			'id' 				=> '',		
			'calls_type'		=> T_('Наименование операции'),
			'call_date'			=> T_('Дата операции'),
			'call_time'			=> T_('Время'),
			'add_money'			=> T_('Сумма начисления'),
			'take_money'		=> T_('Сумма списания'),
			'cost_advertisers'	=> T_('За озвучивание'),			
			'cost_sms' 			=> T_('За SMS'),
			'cost_email'		=> T_('За email'),
			'calls_total'       => '',
		    'withsms'           => '',
		    'withemail'         => '',
		
		);		
		
/*
		$config = array_merge(array('rowsOnPage' => 5, 'order' => 'id'), $config);
		if ($this->input->post('per_page'))
		{
			$config['rowsOnPage'] = $this->input->post('per_page');
			$per_page = 0;
		}		
		else if ($this->input->get('per_page'))
		{
			$per_page = $this->input->get('per_page');
		}
		else
		{
			$per_page = 0;
		}
*/
/*		$order 		= $this->input->get_post('order');
		$direction 	= $this->input->get_post('direction');
		$search 	= $this->input->get_post('search');*/
		
		$from_date 		= (!empty($from_date)) ? $from_date : $this->input->get_post('from_date');
		$to_date        = (!empty($to_date)) ? $to_date : $this->input->get_post('to_date');
		$id_campaign = $this->input->get_post('id_campaign');
		$id_campaign 	= (!empty($id_campaign)) ? $id_campaign : 0;
		
        if (empty($to_date)) {
            $to_date = date('Y-m-d');           
        }       
		
		if (empty($from_date)) {
            $lastmonth = mktime(0, 0, 0, date("m")-3, date("d"), date("Y"));
            $from_date = date('Y-m-d', $lastmonth);
		}
		
		// pagination config
		/*$config['base_url'] = Xurl_add(Xregenerate_url('per_page,search', true), 'search', $search);
		$config['per_page'] = $config['rowsOnPage'];
		
		$this->pagination->initialize($config);
		*/
		// main sql
		$sql = "select * from rpt_financial_period('$from_date', '$to_date', $id, $id_campaign);"; 
		
		
		$this->data['table_list'] = $this->db->query($sql)->result();
		//$this->data['per_page'] 	= $per_page;
		//$this->data['search'] 	= $search;
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;	
		$this->data['id_campaign'] 	= $id_campaign;
		//pr($this->data);
	}
	
	function campaign_graph($id_campaign = 0, $from_date = '', $to_date = '', $id_graph = 1)
	{ 	
		
        if (empty($to_date)) {
            $to_date = date('Y-m-d');           
        }       
		
		if (empty($from_date)) {
            $lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));
            $from_date = date('Y-m-d', $lastmonth);
		}
	
		switch ($id_graph):
		    case 1:
		        $sql = "select 	aggr_time as call_date, 
		        				sum(cost_total) as param
						from stats.advertisers_all
						where id_campaigns = $id_campaign 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		    case 2:
		        $sql = "select 
							cast(call_date as date) as call_date,
						    sum(cost_advertisers) as param   
						from stats.calls_all ca
						where ca.id_campaigns = $id_campaign
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 3:
			    $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_sms) as param   
						from stats.calls_all ca
						where ca.id_campaigns = $id_campaign
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 4:
			    $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_email) as param   
						from stats.calls_all ca
						where ca.id_campaigns = $id_campaign
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 5:
			    $sql = "select 	aggr_time as call_date, 
		        				sum(num_calls) as param
						from stats.advertisers_all
						where id_campaigns = $id_campaign 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		    case 6:
			    $sql = "select 	aggr_time as call_date, 
		        				sum(num_sms) as param
						from stats.advertisers_all
						where id_campaigns = $id_campaign 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		    case 7:
			    $sql = "select 	aggr_time as call_date, 
		        				sum(num_email) as param
						from stats.advertisers_all
						where id_campaigns = $id_campaign 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		endswitch;
		
		$this->data['graphic_table_list'] = $this->db->query($sql)->result();
				
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;	
		$this->data['id_campaign'] 	= $id_campaign;
		$this->data['id_graph'] 	= $id_graph;
		//pr($this->data);
	}
	
	function advert_graph($id_advert = 0, $from_date = '', $to_date = '', $id_graph = 1)
	{ 	
		
        if (empty($to_date)) {
            $to_date = date('Y-m-d');           
        }       
		
		if (empty($from_date)) {
            $lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));
            $from_date = date('Y-m-d', $lastmonth);
		}
	
		switch ($id_graph):
		    case 1:
		        $sql = "select 	aggr_time as call_date, 
						        sum(cost_total) as param
						from stats.advertisers_all
						where id_adverts = $id_advert
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		    case 2:
		        $sql = "select 
							cast(call_date as date) as call_date,
						    sum(cost_advertisers) as param   
						from stats.calls_all ca
						where ca.id_adverts = $id_advert
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 3:
			    $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_sms) as param   
						from stats.calls_all ca
						where ca.id_adverts = $id_advert
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 4:
			    $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_email) as param   
						from stats.calls_all ca
						where ca.id_adverts = $id_advert
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 5:
			    $sql = "select 	aggr_time as call_date, 
		        				sum(num_calls) as param
						from stats.advertisers_all
						where id_adverts = $id_advert 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		    case 6:
			    $sql = "select 	aggr_time as call_date, 
		        				sum(num_sms) as param
						from stats.advertisers_all
						where id_adverts = $id_advert 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		    case 7:
			    $sql = "select 	aggr_time as call_date, 
		        				sum(num_email) as param
						from stats.advertisers_all
						where id_adverts = $id_advert 
						and aggr_time between '$from_date' and '$to_date'
						group by aggr_time
						order by 1";
		        break;
		endswitch;
		
		$this->data['graphic_table_list'] = $this->db->query($sql)->result();
				
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;	
		$this->data['id_advert'] 	= $id_advert;
		$this->data['id_graph'] 	= $id_graph;
		//pr($this->data);
	}
	
	function advertiser_graph($id_advertiser =0, $id_campaign = 0, $from_date = '', $to_date = '', $id_graph = 1)
	{ 	
		
        if (empty($to_date)) {
            $to_date = date('Y-m-d');           
        }       
		
		if (empty($from_date)) {
            $lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));
            $from_date = date('Y-m-d', $lastmonth);
		}
	
		switch ($id_graph):
		    case 1:
		        $sql = "select 	aggr_time as call_date, 
						        sum(cost_total) as param
						from stats.advertisers_all aa
						join campaigns c on c.id = aa.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
							then c.id = $id_campaign 
						    else 1 = 1
						end
						and aggr_time between '$from_date' and '$to_date' 
						group by aggr_time
						order by 1";
		        break;
		    case 2:
		        $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_advertisers) as param   
						from stats.calls_all ca
						join campaigns c on c.id = ca.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
						    then c.id = $id_campaign 
						    else 1 = 1
						end
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 3:
			    $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_sms) as param   
						from stats.calls_all ca
						join campaigns c on c.id = ca.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
						    then c.id = $id_campaign 
						    else 1 = 1
						end
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 4:
			    $sql = "select 
						    cast(call_date as date) as call_date,
						    sum(cost_email) as param   
						from stats.calls_all ca
						join campaigns c on c.id = ca.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
						    then c.id = $id_campaign 
						    else 1 = 1
						end
						and cast(call_date as date) between '$from_date' and '$to_date'
						group by cast(call_date as date)
						order by 1";
		        break;
		    case 5:
			    $sql = "select 	aggr_time as call_date, 
						        sum(num_calls) as param
						from stats.advertisers_all  aa
						join campaigns c on c.id = aa.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
						    then c.id = $id_campaign 
						    else 1 = 1
						end
						and aggr_time between '$from_date' and '$to_date' 
						group by aggr_time
						order by 1";
		        break;
		    case 6:
			    $sql = "select 	aggr_time as call_date, 
						        sum(num_sms) as param
						from stats.advertisers_all  aa
						join campaigns c on c.id = aa.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
						    then c.id = $id_campaign 
						    else 1 = 1
						end
						and aggr_time between '$from_date' and '$to_date' 
						group by aggr_time
						order by 1";
		        break;
		    case 7:
			    $sql = "select 	aggr_time as call_date, 
						        sum(num_email) as param
						from stats.advertisers_all  aa
						join campaigns c on c.id = aa.id_campaigns
						where 
						c.id_advertisers = $id_advertiser
						and 
						case when $id_campaign > 0
						    then c.id = $id_campaign 
						    else 1 = 1
						end
						and aggr_time between '$from_date' and '$to_date' 
						group by aggr_time
						order by 1";
		        break;
		endswitch;
		
		$this->data['graphic_table_list'] = $this->db->query($sql)->result();
				
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;	
		$this->data['id_campaign'] 	= $id_campaign;
		$this->data['id_graph'] 	= $id_graph;
		//pr($this->data);
	}
	
	function user_graph($id_user =0, $from_date = '', $to_date = '', $id_graph = 1)
	{ 	
		
        if (empty($to_date)) {
            $to_date = date('Y-m-d');           
        }       
		
		if (empty($from_date)) {
            $lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));
            $from_date = date('Y-m-d', $lastmonth);
		}
	
		switch ($id_graph):
		    case 1:
		        $sql = "select 	aggr_date as call_date, 
						        sum(bonus_debit) as param
						from stats.users_all ua
						where 
						ua.id_users = $id_user
						and aggr_date between '$from_date' and '$to_date' 
						group by aggr_date
						order by 1";
		        break;
		    case 2:
		        $sql = "select 	aggr_date as call_date, 
						        sum(bonus_credit) as param
						from stats.users_all ua
						where 
						ua.id_users = $id_user
						and aggr_date between '$from_date' and '$to_date' 
						group by aggr_date
						order by 1";
		        break;
		    case 3:
			    $sql = "select 	aggr_date as call_date, 
						        sum(num_adv_calls) as param
						from stats.users_all ua
						where 
						ua.id_users = $id_user
						and aggr_date between '$from_date' and '$to_date' 
						group by aggr_date
						order by 1";
		        break;
		  
		endswitch;
		
		$this->data['graphic_table_list'] = $this->db->query($sql)->result();
				
		$this->data['from_date'] 	= $from_date;
		$this->data['to_date'] 		= $to_date;		
		$this->data['id_graph'] 	= $id_graph;
		//pr($this->data);
	}
	
	function getGraphViews()
 	{	
		$options = array(
              '1'   => 'Денег потраченных на все',
              '2'   => 'Денег потраченых на озвучивание',
              '3'   => 'Денег на SMS',
              '4'   => 'Денег на e-mail',
              '5'   => 'Количество контактов',
              '6'   => 'Количества SMS', 
              '7'   => 'Количество e-mail',
			); 
		return $options;
 	}
 	
  function archive($items_serialized = null)
  {
    $items = explode("&", $items_serialized);
    
    $report = "";
    foreach ($items as $id)
    {  
      $item['id'] = $id;
      $item['status'] = 2;

      $res = $this->db->update($this->table, $item);
      
      if (is_null($res))
      {
        $this->log->writeMessage('error', T_('Error. The Report was not archived.'));
        $report .= ",0"; // don't deleted
      }
      else
      {
      	//$this->db->delete('users_numbers', array('id_users' => $id));
      	
        $this->log->writeMessage('done', T_('The Report was archived.'));
        $report .= ",1"; // deleted        
      } 
    }
    return substr($report, 1);
  }
}
?>